#delimit;
clear;
capture log close;
set more off;
set matsize 5000;

/*****local
CHANGE PATH
*****/;
local pathtab "/data";
local out "/data";
local pathtables "/data";

use "`pathtab'/dataset_for_regs_new_RandR_homestate_noCB.dta", clear;

sort pid;

gen year=2004;

/*Merging CPI. Base=2014:June=1*/
merge m:1 year using "`out'/cpi_tomerge.dta";
drop _merge;
drop year;

/*Definition of state variable to use*/
*local variab="firststate";
drop state;
rename first_col_state_earl_new st_e_new;
rename *_col_state_earl_new *_st_e_new;
local s="st_e_new";

/*Generate Instrument*/
gen month18 = dob_m;
gen year18 = dob_y+18;
replace year18 = year18+1 if month18 > 6; 

egen minyear18=min(year18);
local minyear=minyear18[1];

/*Inputs for instruments*/
/*Here we are using sticker price for public 4 and private 4 from digest of education*/
gen i1_`s'=.;
gen i2_`s'=.;
gen i3_`s'=.;
gen i4_`s'=.;
gen i5_`s'=.;
gen i6_`s'=.;

gen i1_priv4_`s'=.;
gen i2_priv4_`s'=.;
gen i3_priv4_`s'=.;
gen i4_priv4_`s'=.;
gen i5_priv4_`s'=.;
gen i6_priv4_`s'=.;

forvalues yo18=`minyear'(1)2000 {;
local yo19=`yo18'+1;
local yo20=`yo18'+2;
local yo21=`yo18'+3;
local yo22=`yo18'+4;
local yo23=`yo18'+5;

replace i1_`s'=t`yo18'_`s' if year18==`yo18'; /*18*/
replace i2_`s'=t`yo19'_`s' if year18==`yo18'; /*19*/
replace i3_`s'=t`yo20'_`s' if year18==`yo18'; /*20*/
replace i4_`s'=t`yo21'_`s' if year18==`yo18'; /*21*/
replace i5_`s'=t`yo22'_`s' if year18==`yo18'; /*22*/
replace i6_`s'=t`yo23'_`s' if year18==`yo18'; /*23*/

replace i1_priv4_`s'=t_priv4`yo18'_`s' if year18==`yo18'; /*18*/
replace i2_priv4_`s'=t_priv4`yo19'_`s' if year18==`yo18'; /*19*/
replace i3_priv4_`s'=t_priv4`yo20'_`s' if year18==`yo18'; /*20*/
replace i4_priv4_`s'=t_priv4`yo21'_`s' if year18==`yo18'; /*21*/
replace i5_priv4_`s'=t_priv4`yo22'_`s' if year18==`yo18'; /*22*/
replace i6_priv4_`s'=t_priv4`yo23'_`s' if year18==`yo18'; /*23*/
};
/*These are the instruments. 
inst4_`s' is the sum of tuition from ages 18 to 21
inst6_`p;' is the sum of tuition from ages 18 to 23
*/
gen sum_i4_`s'=i1_`s'+i2_`s'+i3_`s'+i4_`s';
gen sum_i6_`s'=i1_`s'+i2_`s'+i3_`s'+i4_`s'+i5_`s'+i6_`s';

gen sum_i4_priv4_`s'=i1_priv4_`s'+i2_priv4_`s'+i3_priv4_`s'+i4_priv4_`s';
gen sum_i6_priv4_`s'=i1_priv4_`s'+i2_priv4_`s'+i3_priv4_`s'+i4_priv4_`s'+i5_priv4_`s'+i6_priv4_`s';

gen t_save_sum_i4 = sum_i4_`s';
gen t_save_sum_i4_priv4 = sum_i4_priv4_`s';
gen debt_save = tot_fedloans_22;

/*Inputs for instruments*/
/*Sticker price from digest of education for public 2*/
gen i1_p2_`s'=.;
gen i2_p2_`s'=.;
gen i3_p2_`s'=.;
gen i4_p2_`s'=.;
gen i5_p2_`s'=.;
gen i6_p2_`s'=.;

forvalues yo18=`minyear'(1)2000 {;
local yo19=`yo18'+1;
local yo20=`yo18'+2;
local yo21=`yo18'+3;
local yo22=`yo18'+4;
local yo23=`yo18'+5;

replace i1_p2_`s'=t_p2`yo18'_`s' if year18==`yo18'; /*18*/
replace i2_p2_`s'=t_p2`yo19'_`s' if year18==`yo18'; /*19*/
replace i3_p2_`s'=t_p2`yo20'_`s' if year18==`yo18'; /*20*/
replace i4_p2_`s'=t_p2`yo21'_`s' if year18==`yo18'; /*21*/
replace i5_p2_`s'=t_p2`yo22'_`s' if year18==`yo18'; /*22*/
replace i6_p2_`s'=t_p2`yo23'_`s' if year18==`yo18'; /*23*/
};
/*These are the instruments. 
inst4_`s' is the sum of tuition from ages 18 to 21
inst6_`p;' is the sum of tuition from ages 18 to 23
*/
gen sum_i2_p2_`s'=i1_p2_`s'+i2_p2_`s';
gen sum_i4_p2_`s'=i1_p2_`s'+i2_p2_`s'+i3_p2_`s'+i4_p2_`s';
gen sum_i6_p2_`s'=i1_p2_`s'+i2_p2_`s'+i3_p2_`s'+i4_p2_`s'+i5_p2_`s'+i6_p2_`s';

gen t_save_sum_i4_p2 = sum_i4_p2_`s';


forvalues yo=22/41 {;
local yo_1=`yo'-1;
gen ccard`yo_1'=((ccard_start1-dob)/365<=`yo' | (ccard_start2-dob)/365<=`yo' | 
	(ccard_start3-dob)/365<=`yo' | (ccard_start4-dob)/365<=`yo' | 
	(ccard_start5-dob)/365<=`yo' | (ccard_start6-dob)/365<=`yo' |
	(ccard_start7-dob)/365<=`yo' | (ccard_start8-dob)/365<=`yo');
replace ccard`yo_1'=. if (day_of_TU_2014-dob)/365<`yo_1';
};

forvalues yo=22/41 {;
local yo_1=`yo'-1;
gen auto`yo_1'=((auto_start1-dob)/365<=`yo' | (auto_start2-dob)/365<=`yo' | 
	(auto_start3-dob)/365<=`yo' | (auto_start4-dob)/365<=`yo' | 
	(auto_start5-dob)/365<=`yo' | (auto_start6-dob)/365<=`yo' |
	(auto_start7-dob)/365<=`yo' | (auto_start8-dob)/365<=`yo' | (auto_start9-dob)/365<=`yo');
replace auto`yo_1'=. if (day_of_TU_2014-dob)/365<`yo_1';
};

forvalues yo=22/41 {;
local yo_1=`yo'-1;
gen install`yo_1'=((install_start1-dob)/365<=`yo' | (install_start2-dob)/365<=`yo' | 
	(install_start3-dob)/365<=`yo' | (install_start4-dob)/365<=`yo' | 
	(install_start5-dob)/365<=`yo' | (install_start6-dob)/365<=`yo' |
	(install_start7-dob)/365<=`yo');
replace install`yo_1'=. if (day_of_TU_2014-dob)/365<`yo_1';
};

forvalues yo=22/41 {;
local yo_1=`yo'-1;
gen lcred`yo_1'=((lcred_start1-dob)/365<=`yo' | (lcred_start2-dob)/365<=`yo' | 
	(lcred_start3-dob)/365<=`yo' | (lcred_start4-dob)/365<=`yo' | 
	(lcred_start5-dob)/365<=`yo');
replace lcred`yo_1'=. if (day_of_TU_2014-dob)/365<`yo_1';
};

forvalues yo=22/41 {;
local yo_1=`yo'-1;
gen unsec`yo_1'=((unsec_start1-dob)/365<=`yo' | (unsec_start2-dob)/365<=`yo' | 
	(unsec_start3-dob)/365<=`yo' | (unsec_start4-dob)/365<=`yo' | 
	(unsec_start5-dob)/365<=`yo' | (unsec_start6-dob)/365<=`yo' |
	(unsec_start7-dob)/365<=`yo' | (unsec_start8-dob)/365<=`yo' | (unsec_start9-dob)/365<=`yo' | (unsec_start10-dob)/365<=`yo');
replace unsec`yo_1'=. if (day_of_TU_2014-dob)/365<`yo_1';
};

forvalues yo=22/41 {;
local yo_1=`yo'-1;
gen secgoods`yo_1'=((secgoods_start1-dob)/365<=`yo' | (secgoods_start2-dob)/365<=`yo' | 
	(secgoods_start3-dob)/365<=`yo' | (secgoods_start4-dob)/365<=`yo' | 
	(secgoods_start5-dob)/365<=`yo' | (secgoods_start6-dob)/365<=`yo' |
	(secgoods_start7-dob)/365<=`yo' | (secgoods_start8-dob)/365<=`yo' | (secgoods_start9-dob)/365<=`yo');
replace secgoods`yo_1'=. if (day_of_TU_2014-dob)/365<`yo_1';
};

forvalues yo=22/41 {;
local yo_1=`yo'-1;
gen lease`yo_1'=((lease_start1-dob)/365<=`yo' | (lease_start2-dob)/365<=`yo' | 
	(lease_start3-dob)/365<=`yo' | (lease_start4-dob)/365<=`yo');
replace lease`yo_1'=. if (day_of_TU_2014-dob)/365<`yo_1';
};

forvalues yo=22/41 {;
local yo_1=`yo'-1;
gen collat`yo_1'=(install`yo_1'==1 | secgoods`yo_1'==1);
replace collat`yo_1'=. if install`yo_1'==. & secgoods`yo_1'==.;
};

forvalues yo=22/41 {;
local yo_1=`yo'-1;
gen own`yo_1'=((mortgage_start1-dob)/365<=`yo' | (mortgage_start2-dob)/365<=`yo' | 
	(mortgage_start3-dob)/365<=`yo' | (mortgage_start4-dob)/365<=`yo' | 
	(mortgage_start5-dob)/365<=`yo' | (mortgage_start6-dob)/365<=`yo' |
	(mortgage_start7-dob)/365<=`yo');
replace own`yo_1'=. if (day_of_TU_2014-dob)/365<`yo_1';


gen house_card`yo_1' = own`yo_1'*ccard`yo_1';
gen house_nocard`yo_1' = own`yo_1'*(ccard`yo_1'==0);
gen nohouse_card`yo_1' = (own`yo_1'==0)*ccard`yo_1';
gen nohouse_nocard`yo_1' = (own`yo_1'==0)*(ccard`yo_1'==0);
};

forvalues yo=22/41 {;
local yo_1= `yo'-1;
gen both`yo_1' = auto`yo_1'*own`yo_1';
gen onlyauto`yo_1' = auto`yo_1'*(own`yo_1'==0);
gen onlyhouse`yo_1' = (auto`yo_1'==0)*own`yo_1';
gen neither`yo_1' = (auto`yo_1'==0)*(own`yo_1'==0);
};


/*TU never sent the records for these following people to NSC, so we should drop them from all regressions*/
gen todrop=(pid=="CQ372100006084" | pid=="CQ372100009510" | pid=="CQ372200002745" | 
pid=="CQ372200003550" | pid=="CQ372400000387" | pid=="CQ372400000600" | pid=="CQ372400001543" | 
pid=="CQ372400003695" | pid=="CQ372400004137" | pid=="CQ372400005128" | pid=="CQ372400005720" | 
pid=="CQ372400006424" | pid=="CQ372500000301" | pid=="CQ372500000380" | pid=="CQ372500001977" | 
pid=="CQ372500006063" | pid=="CQ372500007637" | pid=="CQ372500008864" | pid=="CQ372600001756" |
pid=="CQ372600004821" | pid=="CQ372700001705" | pid=="CQ372700002435" | pid=="CQ372700003380" | 
pid=="CQ372700006709" | pid=="CQ372700006975" | pid=="CQ372800000154" | pid=="CQ372800005549" | 
pid=="CQ372900002337" | pid=="CQ372900004187" | pid=="CQ372900005785" | pid=="CQ372900008630" | 
pid=="CQ373100001269" | pid=="CQ373100001603" | pid=="CQ373100006188" | pid=="CQ373200000787" | 
pid=="CQ373200001594" | pid=="CQ373200002688" | pid=="CQ373200004182" | pid=="CQ373200004581" |
pid=="CQ373200004691" | pid=="CQ373200006989" | pid=="CQ373200007213" | pid=="CQ373200008162" | 
pid=="CQ373200008733" | pid=="CQ373300000792" | pid=="CQ373300002947" | pid=="CQ373300004204" | 
pid=="CQ373300004205" | pid=="CQ373400000626" | pid=="CQ373400002904" | pid=="CQ373500000720" | 
pid=="CQ373600004108" | pid=="CQ373600004759" | pid=="CQ373600006182" | pid=="CQ373600007420" | 
pid=="CQ373700002243" | pid=="CQ373700002715" | pid=="CQ373700005844" | pid=="CQ373700009096" |
pid=="CQ373800002129" | pid=="CQ373800002271" | pid=="CQ373800002364" | pid=="CQ373900003633" | 
pid=="CQ373900006494" | pid=="CQ373900007014" | pid=="9EA00300005576" | pid=="9EA00400005572" | 
pid=="9EA00500002667" | pid=="9EA00500005465" | pid=="9EA00700007009" | pid=="9EA00800009275" | 
pid=="CQ370200000953" | pid=="CQ370200001939" | pid=="CQ370200003277" | pid=="CQ370300005225" | 
pid=="CQ370300007037" | pid=="CQ370300008687" | pid=="CQ370400003593" | pid=="CQ370400005529" |
pid=="CQ370400007514" | pid=="CQ370400008412" | pid=="CQ370500001079" | pid=="CQ370500001258" | 
pid=="CQ370500003356" | pid=="CQ370500006624" | pid=="CQ370500006805" | pid=="CQ370500007723" | 
pid=="CQ370500008189" | pid=="CQ370600001023" | pid=="CQ370600008519" | pid=="CQ370600010249" | 
pid=="CQ370700001187" | pid=="CQ370700004542" | pid=="CQ370700005882" | pid=="CQ370800000424" | 
pid=="CQ370800005683" | pid=="CQ370800006851" | pid=="CQ370900001216" | pid=="CQ370900001674" |
pid=="CQ370900002376" | pid=="CQ370900005657" | pid=="CQ370900008437" | pid=="CQ371100002719" | 
pid=="CQ371100003439" | pid=="CQ371100003597" | pid=="CQ371100004633" | pid=="CQ371100005460" | 
pid=="CQ371100008563" | pid=="CQ371100009361" | pid=="CQ371200004861" | pid=="CQ371200008210" | 
pid=="CQ371200008915" | pid=="CQ371300001384" | pid=="CQ371300001647" | pid=="CQ371300001711" | 
pid=="CQ371300001875" | pid=="CQ371300002833" | pid=="CQ371300004506" | pid=="CQ371300006181" |
pid=="CQ371400004368" | pid=="CQ371400005464" | pid=="CQ371400005694" | pid=="CQ371400007372" | 
pid=="CQ371500000124" | pid=="CQ371500003405" | pid=="CQ371500006187" | pid=="CQ371500007891" | 
pid=="CQ371600000271" | pid=="CQ371600006663" | pid=="CQ371700000393" | pid=="CQ371700003661" | 
pid=="CQ371700004893" | pid=="CQ371700005284" | pid=="CQ371700006966" | pid=="CQ371800001716" | 
pid=="CQ371800004333" | pid=="CQ371800006108" | pid=="CQ371800007280" | pid=="CQ371800007646" |
pid=="CQ371800008647" | pid=="CQ371900003690");

drop if todrop==1;

forvalues yo=21(1)39 {;
/*This variable takes a value of one if the individual is observed at that age and we don't have a value for the debt*/
gen total_debt_`yo'_missing=(total_debt_`yo'==0 & (with_sl_debt_`yo'~=0 | with_auto_debt_`yo'~=0) & noage_`yo'==0); /*Credit card debt can be zero*/
gen auto_debt_`yo'_missing=(auto_debt_`yo'==0 & with_auto_debt_`yo'~=0 & noage_`yo'==0); 
gen sl_debt_`yo'_missing=(sl_debt_`yo'==0 & with_sl_debt_`yo'~=0 & noage_`yo'==0); 
};


/*I will drop "states" that are not states and have very few observations*/
drop if `s'=="FM" | `s'=="GU" | `s'=="VI" | `s'=="PR";
tabulate `s', gen(d_`s');

/*Unable to determine state*/
drop if `s'=="";


tabulate degree_22, gen(d_degree_22);
rename d_degree_221 degree_none_22;
rename d_degree_222 degree_unknown_22;
rename d_degree_223 degree_AS_22;
rename d_degree_224 degree_atleast_AS_22;
rename d_degree_225 degree_BA_22;
rename d_degree_226 degree_atleast_BA_22;
rename d_degree_227 degree_MA_22;

gen degree_butunknown_22=(degree_unknown_22==1 | degree_atleast_AS_22==1 | degree_atleast_BA_22==1);


tabulate major_agg_22, gen(d_major_agg_22);


/*Drop those cases for which I wasn't able to merge the sector*/
drop if ever_with_pub4_22==0 & ever_with_pub2_22==0 & ever_with_priv_nfp4_22==0 & ever_with_priv_nfp2_22==0 & ever_with_priv_fp_22==0 & age_earliest_enrollment_new<23;


forvalues i=22(1)39 {;
gen nocollege_`i' = (ever_with_pub4_`i'==0 & ever_with_pub2_`i'==0 & ever_with_priv_nfp4_`i'==0 & ever_with_priv_nfp2_`i'==0 & ever_with_priv_fp_`i'==0);
local iplus = `i'+1;
replace ccard_lim_`i' = ccard_lim_`iplus' if noage_`i' == 1;
replace ccard_lim_nm_`i' = ccard_lim_nm_`iplus' if noage_`i' == 1;
replace ccard_debt_`i' = ccard_debt_`iplus' if noage_`i' == 1;

replace auto_debt_`i' = auto_debt_`iplus' if noage_`i' == 1;
replace install_debt_`i' = install_debt_`iplus' if noage_`i' == 1;
replace lcred_debt_`i' = lcred_debt_`iplus' if noage_`i' == 1;
replace unsec_debt_`i' = unsec_debt_`iplus' if noage_`i' == 1;
replace secgoods_debt_`i' = secgoods_debt_`iplus' if noage_`i' == 1;
replace lease_debt_`i' = lease_debt_`iplus' if noage_`i' == 1;

replace noage_`i' = noage_`iplus' if noage_`i' == 1;

gen utilization`i' = ccard_debt_`i'/ccard_lim_`i' if  ccard_debt_`i' <= 2*ccard_lim_`i';
gen utilization_nm`i' = ccard_debt_`i'/ccard_lim_nm_`i' if  ccard_debt_`i' <= 2*ccard_lim_nm_`i';

gen collat_debt_`i'=install_debt_`i'+secgoods_debt_`i';
};


forvalues i=22(1)39 {;
gen ccard_debt_n0_`i'=ccard_debt_`i' if ccard_lim_nm_`i'>0 & ccard_lim_nm_`i'<.;
gen ccard_lim_nm_n0_`i'=ccard_lim_nm_`i' if ccard_lim_nm_`i'>0 & ccard_lim_nm_`i'<.;
gen maxed_util_nm`i'=(utilization_nm`i'>=1) if utilization_nm`i'~=.;
gen util1_nm`i'=utilization_nm`i';
replace util1_nm`i'=1 if utilization_nm`i'>1 & utilization_nm`i'<.;
};

egen origin_state = group(`s');

gen double clustvar = origin_state;
gen double clustvar2 = origin_state*10000+dob_y+(month18 > 6);

gen dob_y1973=dob_y==(1973-(month18 > 6));
gen dob_y1974=dob_y==(1974-(month18 > 6));
gen dob_y1975=dob_y==(1975-(month18 > 6));
gen dob_y1976=dob_y==(1976-(month18 > 6));
gen dob_y1977=dob_y==(1977-(month18 > 6));
gen dob_y1978=dob_y==(1978-(month18 > 6));
gen dob_y1979=dob_y==(1979-(month18 > 6));
gen dob_y1980=dob_y==(1980-(month18 > 6));
gen dob_y1981=dob_y==(1981-(month18 > 6));
gen dob_y1982=dob_y==(1982-(month18 > 6));

replace wages22 = wages22/1000;

/***********************
Need to express debt in real terms
Redefine degrees
Redefine ever_with_priv_nfp2
***********************/

gen year=year18+8;  /*Year when person is 26*/
/*Expressing tuition and federal loans in real terms. I use the cpi for the month of June*/
qui sum year;
local miny=r(min);
local maxy=r(max);

sum  sum_i4_`s' tot_fedloans_22;
forvalues yo=`miny'/`maxy' {;
replace sum_i4_`s'=sum_i4_`s'/cpi_j_`yo' if year==`yo';
replace sum_i4_priv4_`s'=sum_i4_priv4_`s'/cpi_j_`yo' if year==`yo';
replace sum_i2_p2_`s'=sum_i2_p2_`s'/cpi_j_`yo' if year==`yo';

replace tot_fedloans_22=tot_fedloans_22/cpi_j_`yo' if year==`yo';
};

forvalues x = 18/32{;
	gen year_age_`x' = dob_y+`x'+(month18 > 6);
	gen pd_`x'=.;
	forvalues y = 1990/2014{;
		replace pd_`x' = 1/cpi_j_`y' if year_age_`x' == `y';
	};
};



gen not_in_nsc=0;
replace not_in_nsc=1 if in_nsc~=1 & nocollege_22==0;


local instruments="sum_i4";
tabulate clustvar, gen(d_clustvar);
tabulate clustvar2, gen(d_clustvar2d);

keep own* tot_fedloans_22 *_save sum_i4* *sum_i4* ever_with*22 nocollege_22 degree*22 pell_22 d_major*22* clustvar todrop f_enroll_grad_new wages22 u_rate22 
corelogic22 pd* dob_y* d_`s'* dob_y month18 clustvar2 auto* ccard* install* lcred* unsec* secgoods* lease* both* onlyauto* onlyhouse* neither* *house_*card* ccard_lim* noage* with_ccard_debt* ccard_debt* utilization* maxed* util1* collat* d_clustvar2d*;
keep if todrop == 0 & f_enroll_grad_new~=1;


/*********
**TTable A5. his is a regression similar to the first stage, but instead of having student loans in the LHS, we have tution at private 4-year schools, to see how correlated tution at differen type of schools are, based on a comment from a referee**
**********/
local age 27;
foreach inst of local instruments {;
forvalues x = `age'/`age'{;
replace tot_fedloans_22 = debt_save*pd_`x';
replace `inst'_`s' = t_save_`inst'*pd_`x';
replace `inst'_priv4_`s' = t_save_`inst'_priv4*pd_`x';
xtset clustvar;

/*"Column (5)": All sample, State and Year FE*/
xtreg `inst'_priv4_`s' `inst'_`s'
dob_y1974-dob_y1982
if todrop == 0 & f_enroll_grad_new~=1, fe vce(cluster clustvar);
outreg2 using "`pathtables'/FirstStage_PrivateTuition_PublicTuition_RandR.xls",
keep(`inst'_`s') 
label replace;

/*Keeping one observation per year-state*/
bys clustvar2: keep if _n==1;  
/*"Column (5)": All sample, State and Year FE*/
xtreg `inst'_priv4_`s' `inst'_`s'
dob_y1974-dob_y1982
if todrop == 0 & f_enroll_grad_new~=1, fe vce(cluster clustvar);
outreg2 using "`pathtables'/FirstStage_PrivateTuition_PublicTuition_RandR.xls",
keep(`inst'_`s') 
label;
 
};
};

capture log close;

